# coding=utf-8
import requests
import pymysql
from scrapy import Selector
import re

conn = pymysql.connect(host="127.0.0.1", user="root", passwd="mysql", db="ip_port", charset="utf8")
cursor = conn.cursor()


def save_ip():
    """从西刺网爬去ip保存ip到数据库"""
    r = requests.get(url='http://www.xicidaili.com', headers={'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.186 Safari/537.36'})
    seletor = Selector(text=r.text)
    reps = seletor.css('#ip_list tr')
    for rep in reps[2:20]:
        r = rep.css('td::text').extract()
        # print(r)
        # print(r[0], r[1], r[3], r[4])
        insert_sql = """
            insert into proxy(ip, port, niming, neixing) values(%s,%s,%s,%s)
        """
        cursor.execute(insert_sql, (r[0], r[1], r[3], r[4]))
        conn.commit()
    conn.close()


class GetIP(object):
    def delete_ip(self, ip):
        """删除不可用的ip"""
        delete_sql = """
            delete from proxy where ip='{0}'
        """.format(ip)
        cursor.execute(delete_sql)
        conn.commit()
        conn.close()
        return True

    def judge_ip(self, ip, port):
        """
        判断ip是否可用
        """
        http_url = 'http://www.baidu.com'
        proxy_url = 'http://%s:%s' % (ip, port)
        try:
            # 定义字典
            dict_url = {
                'http': proxy_url,
            }
            # 构造请求
            responses = requests.get(http_url, proxies=dict_url)
        except Exception as e:
            # print(e)
            # print('hahh')
            self.delete_ip(ip)
            return False
        else:
            # 获取状态码
            code = responses.status_code
            # 判断状态码是否大于200小于300
            if (code >= 200) and (code < 300):
                return True
            else:
                # 删除ip
                self.delete_ip(ip)
                return False

    def get_random_ip(self):
        # 从数据库中随机获取一个可用的ip
        random_sql = """
            select ip,port from proxy where neixing='HTTP' order by rand() LIMIT 1;

        """
        try:
            # 执行sql语句
            ret = cursor.execute(random_sql)
            conn.close()
        except Exception as e:
            print(e)
            return
        # 遍历获取到的值
        for ip_info in cursor.fetchall():
            ip = ip_info[0]
            port = ip_info[1]
            # 调用self.judge_ip方法判断ip是否可用
            judge_re = self.judge_ip(ip, port)
            # 如果为True,直接返回ip和端口
            if judge_re:
                # print('http://{0}:{1}'.format(ip, port))
                return 'http://{0}:{1}'.format(ip, port)
            else:
                self.get_random_ip()


if __name__ == '__main__':
    get_ip = GetIP()
    get_ip.get_random_ip()